package com.huaxia.dao.excellent;

import com.huaxia.pojo.excellent.SaleDeptFHS;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @author wangli 爱我华夏，爱我中华！
 * @date 2020/08/12 17:01
 */
public interface SaleDeptFHSMapper {

    @Select("select t.saledeptsname,t.deptPreNum,(t.deptNowNum-deptPreNum)deptIncreaseNum,t.deptNowNum,round(decode(t.deptPreNum,0,-9999,(t.deptNowNum-deptPreNum)*100/t.deptPreNum),1)saleDeptYield from(\n" +
            "select a.saledeptsname,NVL(b.deptPreNum,0)deptPreNum,NVL(c.deptNowNum,0)deptNowNum from SD_SALEDEPT a left join( \n" +
            "select t.managecom,count(t.managecom) deptPreNum from SD_FHAGENT4 t GROUP BY t.MANAGECOM\n" +
            ")b on a.saledeptcode=b.managecom left join(\n" +
            "select t.managecom,count(t.managecom) deptNowNum from LAFHAGENT t where t.FHAGENTGRADE > 'FH03' and t.OPERATEDATE >=  TRUNC(SYSDATE,'YYYY') \n" +
            " and t.AGENTCODE in (select AGENTCODE from D_AGENT_PROVINCE_YX2 t where t.AGENTSTATE = '在职')  group by t.managecom\n" +
            ")c on a.saledeptcode = c.managecom)t order by saleDeptYield desc")
    List<SaleDeptFHS> getSaleDeptFHS();
}
